/*
 * Copyright 2004 original author or authors.
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.extremecomponents.table.view;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFPrintSetup;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.extremecomponents.table.bean.Column;
import org.extremecomponents.table.calc.CalcResult;
import org.extremecomponents.table.calc.CalcUtils;
import org.extremecomponents.table.core.PreferencesConstants;
import org.extremecomponents.table.core.TableModel;
import org.extremecomponents.util.ExtremeUtils;

/**
 * com.extremecomp.table.view.XlsView.java -
 *
 * @author paul horn
 */
public class XlsxView implements View {
    private static Log                 logger                 = LogFactory
                                                                  .getLog(XlsxView.class);
    // width per char
    public static final int            WIDTH_MULT             = 256;
    // minimum char width
    public static final int            MIN_CHARS              = 8;
    public static final short          DEFAULT_FONT_HEIGHT    = 8;
    public static final double         NON_NUMERIC            = -.99999;
    public static final String         DEFAULT_MONEY_FORMAT   = "$###,###,##0.00";
    public static final String         DEFAULT_PERCENT_FORMAT = "##0.0%";
    public static final String         NBSP                   = "&nbsp;";

    private XSSFWorkbook               wb;
    private XSSFSheet                  sheet;
    private XSSFPrintSetup             ps;
    private Map<String, XSSFCellStyle> styles;
    private int                        rownum;
    private int                        cellnum;
    private XSSFRow                    xssfRow;
    private String                     moneyFormat;
    private String                     percentFormat;

    public XlsxView() {
    }

    public void beforeBody(TableModel model) {
        logger.debug("XlsView.init()");

        moneyFormat = model.getPreferences().getPreference(
            PreferencesConstants.TABLE_EXPORTABLE + "format.money");
        if (StringUtils.isEmpty(moneyFormat)) {
            moneyFormat = DEFAULT_MONEY_FORMAT;
        }
        percentFormat = model.getPreferences().getPreference(
            PreferencesConstants.TABLE_EXPORTABLE + "format.percent");
        if (StringUtils.isEmpty(percentFormat)) {
            percentFormat = DEFAULT_PERCENT_FORMAT;
        }

        wb = new XSSFWorkbook();
        sheet = wb.createSheet();

        wb.setSheetName(0, "Export Workbook");

        styles = initStyles(wb);
        ps = sheet.getPrintSetup();

        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        createHeader(model);
    }

    public void body(TableModel model, Column column) {
        if (column.isFirstColumn()) {
            rownum++;
            cellnum = 0;
            xssfRow = sheet.createRow(rownum);
        }

        // 使用column的value作为导出值
        String value = column.getValueAsString();
        if (StringUtils.isBlank(value)) {
            value = column.getCellDisplay();
        }
        value = ExportViewUtils.parseXLS(StringUtils.trim(value));

        XSSFCell xssfCell = xssfRow.createCell(cellnum);

        if (column.isEscapeAutoFormat()) {
            writeToCellAsText(xssfCell, value, "");
        } else {
            writeToCellFormatted(xssfCell, value, "");
        }
        cellnum++;
    }

    public Object afterBody(TableModel model) {
        if (model.getLimit().getTotalRows() != 0) {
            totals(model);
        }
        return wb;
    }

    private void createHeader(TableModel model) {
        rownum = 0;
        cellnum = 0;
        XSSFRow row = sheet.createRow(rownum);

        @SuppressWarnings("unchecked")
        List<Column> columns = model.getColumnHandler().getHeaderColumns();
        for (Iterator<Column> iter = columns.iterator(); iter.hasNext();) {
            Column column = (Column) iter.next();
            String title = column.getCellDisplay();
            XSSFCell xssfCell = row.createCell(cellnum);

            xssfCell.setCellStyle((XSSFCellStyle) styles.get("titleStyle"));
            xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING);
            xssfCell.setCellValue(title);
            int valWidth = (title + "").getBytes().length * WIDTH_MULT;
            sheet.setColumnWidth(xssfCell.getColumnIndex(), valWidth);

            cellnum++;
        }
    }

    private void writeToCellAsText(XSSFCell cell, String value,
        String styleModifier) {
        // format text
        if (value.trim().equals(NBSP)) {
            value = "";
        }
        cell.setCellStyle((XSSFCellStyle) styles.get("textStyle"
            + styleModifier));
        fixWidthAndPopulate(cell, NON_NUMERIC, value);
    }

    private void writeToCellFormatted(XSSFCell cell, String value,
        String styleModifier) {
        double numeric = NON_NUMERIC;

        try {
            numeric = Double.parseDouble(value);
        } catch (Exception e) {
            numeric = NON_NUMERIC;
        }

        if (value.startsWith("$") || value.endsWith("%")
            || value.startsWith("($")) {
            boolean moneyFlag = (value.startsWith("$") || value
                .startsWith("($"));
            boolean percentFlag = value.endsWith("%");

            value = StringUtils.replace(value, "$", "");
            value = StringUtils.replace(value, "%", "");
            value = StringUtils.replace(value, ",", "");
            value = StringUtils.replace(value, "(", "-");
            value = StringUtils.replace(value, ")", "");

            try {
                numeric = Double.parseDouble(value);
            } catch (Exception e) {
                numeric = NON_NUMERIC;
            }

            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);

            if (moneyFlag) {
                // format money
                cell.setCellStyle((XSSFCellStyle) styles.get("moneyStyle"
                    + styleModifier));
            } else if (percentFlag) {
                // format percent
                numeric = numeric / 100;
                cell.setCellStyle((XSSFCellStyle) styles.get("percentStyle"
                    + styleModifier));
            }
        } else if (numeric != NON_NUMERIC) {
            // format numeric
            cell.setCellStyle((XSSFCellStyle) styles.get("numericStyle"
                + styleModifier));
        } else {
            // format text
            if (value.trim().equals(NBSP)) {
                value = "";
            }
            cell.setCellStyle((XSSFCellStyle) styles.get("textStyle"
                + styleModifier));
        }

        fixWidthAndPopulate(cell, numeric, value);
    }

    private void fixWidthAndPopulate(XSSFCell cell, double numeric, String value) {
        int valWidth = 0;

        if (numeric != NON_NUMERIC) {
            cell.setCellValue(numeric);
            valWidth = (cell.getNumericCellValue() + "$,.").length()
                * WIDTH_MULT;
        } else {
            cell.setCellValue(value);
            valWidth = (cell.getStringCellValue() + "").getBytes().length
                * WIDTH_MULT;

            if (valWidth < (WIDTH_MULT * MIN_CHARS)) {
                valWidth = WIDTH_MULT * MIN_CHARS;
            }
        }

        if (valWidth > sheet.getColumnWidth(cell.getColumnIndex())) {
            sheet.setColumnWidth(cell.getColumnIndex(), valWidth);
        }
    }

    private Map<String, XSSFCellStyle> initStyles(XSSFWorkbook wb) {
        return initStyles(wb, DEFAULT_FONT_HEIGHT);
    }

    private Map<String, XSSFCellStyle> initStyles(XSSFWorkbook wb,
        short fontHeight) {
        Map<String, XSSFCellStyle> result = new HashMap<String, XSSFCellStyle>();
        XSSFCellStyle titleStyle = wb.createCellStyle();
        XSSFCellStyle textStyle = wb.createCellStyle();
        XSSFCellStyle boldStyle = wb.createCellStyle();
        XSSFCellStyle numericStyle = wb.createCellStyle();
        XSSFCellStyle numericStyleBold = wb.createCellStyle();
        XSSFCellStyle moneyStyle = wb.createCellStyle();
        XSSFCellStyle moneyStyleBold = wb.createCellStyle();
        XSSFCellStyle percentStyle = wb.createCellStyle();
        XSSFCellStyle percentStyleBold = wb.createCellStyle();

        // Add to export totals
        XSSFCellStyle moneyStyle_Totals = wb.createCellStyle();
        XSSFCellStyle naStyle_Totals = wb.createCellStyle();
        XSSFCellStyle numericStyle_Totals = wb.createCellStyle();
        XSSFCellStyle percentStyle_Totals = wb.createCellStyle();
        XSSFCellStyle textStyle_Totals = wb.createCellStyle();

        result.put("titleStyle", titleStyle);
        result.put("textStyle", textStyle);
        result.put("boldStyle", boldStyle);
        result.put("numericStyle", numericStyle);
        result.put("numericStyleBold", numericStyleBold);
        result.put("moneyStyle", moneyStyle);
        result.put("moneyStyleBold", moneyStyleBold);
        result.put("percentStyle", percentStyle);
        result.put("percentStyleBold", percentStyleBold);

        // Add to export totals
        result.put("moneyStyle_Totals", moneyStyle_Totals);
        result.put("naStyle_Totals", naStyle_Totals);
        result.put("numericStyle_Totals", numericStyle_Totals);
        result.put("percentStyle_Totals", percentStyle_Totals);
        result.put("textStyle_Totals", textStyle_Totals);

        XSSFDataFormat format = wb.createDataFormat();

        // Global fonts
        XSSFFont font = wb.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        font.setColor(IndexedColors.BLACK.getIndex());
        font.setFontName(XSSFFont.DEFAULT_FONT_NAME);
        font.setFontHeightInPoints(fontHeight);

        XSSFFont fontBold = wb.createFont();
        fontBold.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        fontBold.setColor(IndexedColors.BLACK.getIndex());
        fontBold.setFontName(XSSFFont.DEFAULT_FONT_NAME);
        fontBold.setFontHeightInPoints(fontHeight);

        // Money Style
        moneyStyle.setFont(font);
        moneyStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        moneyStyle.setDataFormat(format.getFormat(moneyFormat));

        // Money Style Bold
        moneyStyleBold.setFont(fontBold);
        moneyStyleBold.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));

        // Percent Style
        percentStyle.setFont(font);
        percentStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        percentStyle.setDataFormat(format.getFormat(percentFormat));

        // Percent Style Bold
        percentStyleBold.setFont(fontBold);
        percentStyleBold.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        percentStyleBold.setDataFormat(format.getFormat(percentFormat));

        // Standard Numeric Style
        numericStyle.setFont(font);
        numericStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);

        // Standard Numeric Style Bold
        numericStyleBold.setFont(fontBold);
        numericStyleBold.setAlignment(XSSFCellStyle.ALIGN_RIGHT);

        // Title Style
        titleStyle.setFont(font);
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT
            .getIndex());
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        titleStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        titleStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        titleStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        titleStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        titleStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        titleStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        titleStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        // Standard Text Style
        textStyle.setFont(font);
        textStyle.setWrapText(true);

        // Standard Text Style
        boldStyle.setFont(fontBold);
        boldStyle.setWrapText(true);

        // Money Style Total
        moneyStyle_Totals.setFont(fontBold);
        moneyStyle_Totals.setFillForegroundColor(IndexedColors.GREY_25_PERCENT
            .getIndex());
        moneyStyle_Totals.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        moneyStyle_Totals.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        moneyStyle_Totals.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        moneyStyle_Totals.setBorderTop(XSSFCellStyle.BORDER_THIN);
        moneyStyle_Totals.setTopBorderColor(IndexedColors.BLACK.getIndex());
        moneyStyle_Totals.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        moneyStyle_Totals.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        moneyStyle_Totals.setDataFormat(format.getFormat(moneyFormat));

        // n/a Style Total
        naStyle_Totals.setFont(fontBold);
        naStyle_Totals.setFillForegroundColor(IndexedColors.GREY_25_PERCENT
            .getIndex());
        naStyle_Totals.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        naStyle_Totals.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        naStyle_Totals.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        naStyle_Totals.setBorderTop(XSSFCellStyle.BORDER_THIN);
        naStyle_Totals.setTopBorderColor(IndexedColors.BLACK.getIndex());
        naStyle_Totals.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        naStyle_Totals.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        // Numeric Style Total
        numericStyle_Totals.setFont(fontBold);
        numericStyle_Totals
            .setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        numericStyle_Totals.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        numericStyle_Totals.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        numericStyle_Totals
            .setBottomBorderColor(IndexedColors.BLACK.getIndex());
        numericStyle_Totals.setBorderTop(XSSFCellStyle.BORDER_THIN);
        numericStyle_Totals.setTopBorderColor(IndexedColors.BLACK.getIndex());
        numericStyle_Totals.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        numericStyle_Totals.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        // Percent Style Total
        percentStyle_Totals.setFont(fontBold);
        percentStyle_Totals
            .setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        percentStyle_Totals.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        percentStyle_Totals.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        percentStyle_Totals
            .setBottomBorderColor(IndexedColors.BLACK.getIndex());
        percentStyle_Totals.setBorderTop(XSSFCellStyle.BORDER_THIN);
        percentStyle_Totals.setTopBorderColor(IndexedColors.BLACK.getIndex());
        percentStyle_Totals.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
        percentStyle_Totals.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        percentStyle_Totals.setDataFormat(format.getFormat(percentFormat));

        // Text Style Total
        textStyle_Totals.setFont(fontBold);
        textStyle_Totals.setFillForegroundColor(IndexedColors.GREY_25_PERCENT
            .getIndex());
        textStyle_Totals.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        textStyle_Totals.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        textStyle_Totals.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        textStyle_Totals.setBorderTop(XSSFCellStyle.BORDER_THIN);
        textStyle_Totals.setTopBorderColor(IndexedColors.BLACK.getIndex());
        textStyle_Totals.setAlignment(XSSFCellStyle.ALIGN_LEFT);
        textStyle_Totals.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        return result;
    }

    // Add to export totals
    public void totals(TableModel model) {
        Column firstCalcColumn = model.getColumnHandler().getFirstCalcColumn();
        if (firstCalcColumn != null) {
            int rows = firstCalcColumn.getCalc().length;
            for (int i = 0; i < rows; i++) {
                rownum++;
                XSSFRow row = sheet.createRow(rownum);
                cellnum = 0;
                for (@SuppressWarnings("unchecked")
                Iterator<Column> iter = model.getColumnHandler().getColumns()
                    .iterator(); iter.hasNext();) {
                    Column column = (Column) iter.next();
                    if (column.isFirstColumn()) {
                        String calcTitle = CalcUtils
                            .getFirstCalcColumnTitleByPosition(model, i);
                        XSSFCell cell = row.createCell(cellnum);
                        if (column.isEscapeAutoFormat()) {
                            writeToCellAsText(cell, calcTitle, "_Totals");
                        } else {
                            writeToCellFormatted(cell, calcTitle, "_Totals");
                        }
                        cellnum++;
                        continue;
                    }

                    if (column.isCalculated()) {
                        CalcResult calcResult = CalcUtils
                            .getCalcResultsByPosition(model, column, i);
                        Number value = calcResult.getValue();
                        XSSFCell cell = row.createCell(cellnum);
                        if (value != null)
                            if (column.isEscapeAutoFormat()) {
                                writeToCellAsText(cell, value.toString(),
                                    "_Totals");
                            } else {
                                writeToCellFormatted(
                                    cell,
                                    ExtremeUtils.formatNumber(
                                        column.getFormat(), value,
                                        model.getLocale()), "_Totals");
                            }
                        else {
                            cell.setCellStyle((XSSFCellStyle) styles
                                .get("naStyle_Totals"));
                            cell.setCellValue("n/a");
                        }
                        cellnum++;
                    } else {
                        XSSFCell cell = row.createCell(cellnum);
                        writeToCellFormatted(cell, "", "_Totals");
                        cellnum++;
                    }
                }
            }
        }

    }
}
